<?php

namespace XWAM\Model;

/**
 * CustomerImportModel 类
 *
 * @since VER:1.0; DATE:2016-3-25; AUTHOR:SoChishun; EMAIL:14507247@qq.com; DESC:Added.
 */
class CustomerImportModel extends \Think\Model {

    protected $tableName = 't_porg_customer';

    /**
     * 上传Excel文件
     * @param type $error
     * @param type $inputname
     * @return boolean
     * @since 2016-3-25 SoChishun Added.
     */
    function upload_excel(&$error, $inputname = 'xlsFile') {
        //图片上传
        $dir = './Uploads/CustomerImport';
        if (!is_dir($dir)) {
            mkdir($dir, 0777, true);
        }
        $files = \Common\Controller\UploadHandlerController::upload(array('savePath' => 'CustomerImport/', 'autoSub' => false, 'skipEmpty' => true, 'exts' => array('xls', 'xlsx')));
        if (is_array($files)) {
            return current($files);
        } else {
            $error = $files;
        }
        return false;
    }

    /**
     * 读取Excel文件
     * @param type $path
     * @param type $uid
     * @param type $site_id
     * @return type
     * @since 1.0 2016-3-25 SoChishun Added.
     */
    function read_excel($path, $uid, $site_id) {
        // 判断文件是否存在
        if (!$path || !file_exists($path)) {
            die('文件不存在!');
        }
        // PHPExcel配置
        $options = array(
            'START' => 2, // 起始数据行,默认2(第一行为标题)
            'COUNT' => 0, // 数量,0=不限
            'ActiveSheet' => 0, // 页面索引,默认0
        );
        // 实例化PHPExcel
        vendor('PHPExcel.PHPExcel');
        $objPHPExcel = \PHPExcel_IOFactory::load($path);
        $sheet = $objPHPExcel->getActiveSheet($options['ActiveSheet']);
        $highestRow = $sheet->getHighestRow(); // 取得总行数,数值,如:30
        $highestColumnName = $sheet->getHighestColumn(); // 取得总列数,字符,如:F
        $highestColumn = \PHPExcel_Cell::columnIndexFromString($highestColumnName); // 转换成总列数数值
        $i = $options['START'];
        $count = $options['COUNT'];
        $end = $count;
        if ($i >= $highestRow) {
            return; // Excel文件无内容 2016-4-19
        }
        $m_attr = new CustomerAttrModel();
        $afields = $m_attr->get_customer_import_fields($site_id, array('name' => '姓名', 'telphone' => '电话', 'sex' => '性别', 'address' => '地址'));
        $afields = $afields['custom'] ? ($afields['system'] + $afields['custom']) : $afields['system'];
        $fields = array();
        // 获取所有列
        for ($c = 0; $c < $highestColumn; $c++) {
            $title = $sheet->getCellByColumnAndRow($c, 1)->getValue(); // 格式化标题行
            if ($title instanceof \PHPExcel_RichText) { //富文本转换字符串
                $title = $title->__toString();
            }
            if (!in_array($title, $afields)) {
                continue; // 标题不在字段范围内
            }
            if (false !== ($field = array_search($title, $afields))) {
                $fields[$c] = $field; // 依序添加到写入字段集合中
            }
        }
        if (!$fields) {
            return; // excel表格格式有误,直接打断循环,提高效率 2016-4-19 SoChishun Added.
        }
        // 导出语句：name as '姓名',telphone as '电话',sex as '性别',address as '地址'
        // 内容列格式：姓名，电话，地址，性别        
        while ($i <= $highestRow) {
            if ($count > 0 && ($i - 2) > $count) {
                break;
            }
            foreach ($fields as $c => $field) {
                $cell = $sheet->getCellByColumnAndRow($c, $i);
                $value = $cell->getValue();
                if ($value instanceof \PHPExcel_RichText) { //富文本转换字符串
                    $value = $value->__toString();
                }
                if ($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC) { // 格式化日期
                    $cellstyleformat = $cell->getStyle()->getNumberFormat();
                    $formatcode = $cellstyleformat->getFormatCode();
                    if (preg_match('/^(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy]/i', $formatcode)) {
                        $value = gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value));
                    } else {
                        $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $formatcode);
                    }
                }
                $aval[$field] = $value;
            }
            $avals[] = "('" . implode("','", $aval) . "')";
            unset($aval);
            $i++;
        }
        if (!$avals) {
            return; // excel表格格式不符合字段范围
        }
        // SQL语句模板
        // 使用合并数据+事务方式提高插入效率(http://tech.uc.cn/?p=634)        
        $table = 't_porg_customer_import_u' . $uid;
        $sqlfield = 'insert into ' . $table . ' (' . implode(',', $fields) . ') values ';
        $count = count($avals);
        $n = 0;
        $advm = new \Think\Model\AdvModel();
        for ($i = 0; $i < $count; $i++) {
            if ($i % 200 < 1 && $i > 0) {
                $asqls[] = $sqlfield . implode(',', array_slice($avals, 0, 200));
                array_splice($avals, 0, 200);
                $n++;
            }
            if ($n % 20 < 1 && $n > 0) {
                $advm->patchQuery($asqls);
                unset($asqls);
            }
        }
        if (count($avals) > 0) {
            $sql = $sqlfield . implode(',', $avals);
            $advm->patchQuery(array($sql));
            $n++;
        }
    }

    /**
     * 写入到主表
     * @param type $uid
     * @param type $site_id
     * @param type $user_name
     * @return type
     * @since 1.0 2016-3-25 SoChishun Added.
     */
    function insert_db($uid, $site_id, $user_name) {
        // 过滤数据库中已经有的    
        $table_tmp = 't_porg_customer_import_u' . $uid;
        $table = $this->tableName;
        $count = $this->table($table_tmp)->count();
        // 删除重复
        $del_count = $this->execute("delete $table_tmp from $table_tmp INNER JOIN $table on $table_tmp.telphone=$table.telphone where $table.user_name='$user_name';");
        // 写入到主表
        $m_attr = new CustomerAttrModel();
        $afields = $m_attr->get_customer_import_fields($site_id);
        $field = implode(',', array_keys($afields['system']));
        $serial_no = 'K' . (is_numeric($user_name) ? 'N' . $user_name : $uid) . date('YmdHi');
        $this->execute("insert into $table (serial_no, is_shared, status, site_id, $field) select concat('$serial_no',id), 'Y', 1, $site_id, $field from $table_tmp");
        // 写入到扩展表
        if ($afields['customer']) {
            // 更新临时表的客户编号
            $this->execute("update $table_tmp left join $table on $table_tmp.telphone=$table.telphone set $table_tmp.customer_id=$table.id where $table.user_name='$user_name';");
            $table = 't_porg_customer_s' . $site_id;
            $field = 'customer_id, ' . implode(',', array_keys($afields['custom']));
            $this->execute('insert into ' . $table . ' (' . $field . ') select ' . $field . ' from ' . $table_tmp);
        }
        return array('count' => $count, 'delete' => $del_count);
    }

    /**
     * 创建内存临时表
     * @param type $uid
     * @param integer $site_id
     * @since 1.0 2016-4-19 SoChishun Added.
     * @since 2.0 2016-5-11 SoChishun 重构.
     */
    function create_temp_table($uid, $site_id) {
        $m_attr = new CustomerAttrModel();
        $afields = $m_attr->get_customer_import_script($site_id);
        $script = implode(',', $afields);
        $table = 't_porg_customer_import_u' . $uid;
        $this->execute('drop table if exists ' . $table . ';');
        $script = 'create table if not exists ' . $table . ' (id int auto_increment primary key, customer_id int, ' . $script . ', INDEX idx_customer_id (customer_id), INDEX idx_telphone (telphone)) ENGINE=MEMORY  default CHARSET=utf8;';
        $this->execute($script);
        return true;

        /*
          $m_attr = new CustomerAttrModel();
          $afields = $m_attr->get_customer_import_fields(array('name' => '姓名', 'telphone' => '电话', 'sex' => '性别', 'address' => '地址'));
          $fields = array_keys($afields);
          $list = $this->query("select concat(COLUMN_NAME, ' ', COLUMN_TYPE) as col  from information_schema.`COLUMNS` where TABLE_SCHEMA='db_xcrm_u1' and TABLE_NAME='t_porg_customer' and COLUMN_NAME in ('" . implode("','", $fields) . "');");
          foreach ($list as $row) {
          $sqls[] = $row['col'];
          }
          $table = 't_porg_customer_import_' . $uid;
          $this->execute("drop table if exists $table;"); // 丢弃表
          $sql = 'create table ' . $table . ' (' . implode(',', $sqls) . ',INDEX idx_telphone (telphone)) ENGINE=MEMORY  default CHARSET=utf8;';
          $this->execute($sql);
         */
    }

    /**
     * 丢弃内存临时表
     * @param type $uid
     * @since 1.0 2016-5-11 SoChishun Added.
     */
    function drop_temp_table($uid) {
        $table = 't_porg_customer_import_u' . $uid;
        $this->execute('drop table if exists ' . $table);
    }

}
